﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace BiffenService.Db
{
    public class DbClient
    {
        //public static readonly string _connectionString = @"Data Source=balder.ucn.dk;Initial Catalog=DM75_4;Integrated Security=True;User Id=DM75_4;Password=MaaGodt";
        public static readonly string _connectionString = @"Server=balder.ucn.dk;Database=DM75_4;User ID=DM75_4;Password=MaaGodt;Trusted_Connection=False;";
        //public static readonly string _connectionString = @"Data Source=k4emic-notebook\SQLEXPRESS;Initial Catalog=biffen;Integrated Security=True";

        public static readonly SqlConnection _dbConn = new SqlConnection(_connectionString);
        private static SqlCommand _dbCmd;
        private static SqlTransaction transaction;

        public static SqlTransaction BeginTransaction()
        {
            Open();
            transaction = _dbConn.BeginTransaction();
            return transaction;
        }

        /// <summary>
        /// Method to open a connection to the database
        /// </summary>
        public static void Open()
        { 
            if (_dbConn.State != System.Data.ConnectionState.Open)
                _dbConn.Open();
        }

        /// <summary>
        /// Method to close the connection to the database
        /// </summary>
        public static void Close()
        {
            _dbConn.Close();
        }

        /// <summary>
        /// Method that generates an SQL command
        /// </summary>
        /// <param name="sql">String sql a string that contains the sql statement that should be performed in the database</param>
        /// <returns>SqlCommand _dbCmd </returns>
        public static SqlCommand GetDbCommand(string sql)
        {
            Open();
            if (_dbCmd == null)
            {
                _dbCmd = new SqlCommand(sql, _dbConn, transaction);
            }
            _dbCmd.Parameters.Clear();  
            _dbCmd.CommandText = sql;
            _dbCmd.Transaction = transaction;

            return _dbCmd;
        }
    }
}
